package com.example.universita.esami;

import java.util.ArrayList;
import java.util.List;
import java.io.*;


import android.os.Bundle;
import android.content.*;
import android.content.res.Configuration;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.annotation.SuppressLint;
import android.annotation.TargetApi;
import android.app.Activity;
import android.app.ProgressDialog;
import android.view.*;
import android.view.inputmethod.EditorInfo;
import android.widget.*;
import android.text.*;

public class DBDataUniversity{  

    SQLiteDatabase mDb;
    DbHelper mDbHelper;
    Context mContext;
    private static final String DB_NAME="iLibretto.db";
    private static final int DB_VERSION=1; 
    
    public DBDataUniversity(Context ctx){
            mContext=ctx;
            mDbHelper=new DbHelper(ctx, DB_NAME, null, DB_VERSION);   
    }
    
    public void open(){ 
            mDb=mDbHelper.getWritableDatabase();
            
    }
    
    public void close(){ 
            mDb.close();
    }
    

    public class DbHelper extends SQLiteOpenHelper {

    		private static final String CREATE_TABLE_FACULTY = "CREATE TABLE faculty (id integer primary key, name text not null);";
    		private static final String CREATE_TABLE_COURSE = "CREATE TABLE course (id integer primary key, name text not null, whatFaculty integer, duration integer);";
    		private static final String CREATE_TABLE_SPECIAL = "CREATE TABLE special (id integer primary key, name text not null, whatCourse integer, duration integer);";
    		private static final String CREATE_TABLE_EXAMS = "CREATE TABLE exams (id integer primary key autoincrement, name text not null, credits integer not null, semester integer not null, " +
    				"                   year integer not null, note text default 'No', data text default 'No', vote integer default 0, id_prof integer not null, " +
    				"                   lode integer default 0, type integer not null default 0, acronym text);";
		    private static final String CREATE_TABLE_PROF = "CREATE TABLE docenti (id integer primary key autoincrement, name text not null, mail text default 'No', meeting text default 'No', phone text default 'No');"; 
    		
            public DbHelper(Context context, String name, CursorFactory factory,int version) {
                    super(context, name, factory, version);
            }

            @Override
            public void onCreate(SQLiteDatabase _db) {
                    _db.execSQL(CREATE_TABLE_FACULTY);
                    _db.execSQL(CREATE_TABLE_COURSE);
                    _db.execSQL(CREATE_TABLE_SPECIAL);
                    _db.execSQL(CREATE_TABLE_EXAMS);
                    _db.execSQL(CREATE_TABLE_PROF);
            }

            @Override
            public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
                  

            }

    }
            
    public void insertFaculty(int id, String name){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("id", id);
		mDb.insert("faculty", null, cv);
	}
    
    public boolean haveVoto(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("vote");
    			if(c.getInt(nameCol) != 0)
    				return true;
    			else
    				return false;
    		}while(c.moveToNext());
    	return false;
    }
    
    public int getDurationExam(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM course where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("duration");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int getDurationExamSpecial(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM special WHERE name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("duration");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
	
	public void insertCourse(int id, String name, int whatFaculty, int durata){
		ContentValues cv = new ContentValues();
		cv.put("duration", durata);
		cv.put("name", name);
		cv.put("whatFaculty", whatFaculty);
		cv.put("id", id);
		mDb.insert("course", null, cv);
	}
	
	public void insertSpecial(int id, String name, int whatCourse, int durata){
		ContentValues cv = new ContentValues();
		cv.put("duration", durata);
		cv.put("name", name);
		cv.put("whatCourse", whatCourse);
		cv.put("id", id);
		mDb.insert("special", null, cv);
	}
    
	public void insertNote(String esame, String note){
		
		mDb.execSQL("UPDATE exams SET note = '"+note+"' WHERE exams.name = '"+esame+"';");
		
	}
	
    public int convertIdItemInt(String str){
    	return Integer.parseInt(str.substring(0, str.indexOf(" ")-1));
    }
    
    public void loadToListFaculty(ArrayList<String> l, String n){
    	Cursor c;
    	int nameCol;
    	
    	l.removeAll(l);
    	l.add(n);
    	
    	c = mDb.rawQuery("SELECT * FROM faculty;", null);
    	
    	if(c.moveToFirst())
    		do{
    	        nameCol = c.getColumnIndex("name");
    			l.add(c.getString(nameCol));
    		}while(c.moveToNext());
    }
    
    public void loadToListCourse(ArrayList<String> l, int whatFaculty, String n){
    	Cursor c;
    	int nameCol;
    	
    	l.removeAll(l);
    	l.add(n);
    	
    	c = mDb.rawQuery("SELECT * FROM course WHERE whatFaculty = "+ whatFaculty + ";", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("name");
    			l.add(c.getString(nameCol));
    		}while(c.moveToNext());
    }
    
    public void loadToListSpecial(ArrayList<String> l, int whatCourse, String n){
    	Cursor c;
    	int nameCol;
    	
    	l.removeAll(l);
    	l.add(n);
    	
    	c = mDb.rawQuery("SELECT * FROM special WHERE whatCourse = "+ whatCourse + ";", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("name");
    			l.add(c.getString(nameCol));
    		}while(c.moveToNext());
    }
    
    public int getIdFaculty(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM faculty where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int getIdCourse(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM course where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	
    	return 0;
    }
    
    public int getIdExam(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public String getDate(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("data");
    	    	return c.getString(nameCol);
    		}while(c.moveToNext());
    	return "no data";
    }
    
    public int getCredits(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("credits");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int getVoto(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("vote");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int getLode(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("lode");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public String getProf(String name){
    	Cursor c;
    	Cursor c2;
    	int nameCol, namecol2;
    	int d;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id_prof");
    	    	d = c.getInt(nameCol);
    	    	
    	    	c2 = mDb.rawQuery("SELECT * FROM docenti where id = "+d+" ;", null);
    	    	if(c2.moveToFirst())
    	    		do{
    	    			namecol2 = c2.getColumnIndex("name");
		    	    	return c2.getString(namecol2);
    	    		}while(c2.moveToNext());
    	    	
    		}while(c.moveToNext());
    	return "no prof";
    }
    
    public int getIdSpecial(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM special where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    	
    }
    
    public void deleteAllFaculty(){
        mDb.delete("faculty", null, null);
    }
    
    public void deleteAllCourse(){
        mDb.delete("course", null, null);
    }
    
    public void deleteAllSpecial(){
        mDb.delete("special", null, null);
    }
    
    public void insertExam(String name, int crediti, int anno, int semestre, int id_doc){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("credits", crediti);
		cv.put("year", anno);
		cv.put("semester", semestre);
		cv.put("id_prof", id_doc);
		mDb.insert("exams", null, cv);
	}
    
    public void getNameAllExam(String[] elenco_esami){
    	int i=0;
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams ORDER BY name;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("name");
    			elenco_esami[i] = c.getString(nameCol);
    			nameCol = c.getColumnIndex("id");
    			elenco_esami[i] = elenco_esami[i].concat(" "+c.getInt(nameCol));
    			i++;
    		}while(c.moveToNext());
    	
    }
    
    public Cursor getExams(){
    	
    	Cursor c;
    	c = mDb.rawQuery("SELECT * FROM exams ORDER BY name;", null);
    	return c;
    	
    }
    
public Cursor getCarriera(){
    	
    	Cursor c;
    	c=mDb.rawQuery("SELECT name, credits, year, semester, vote FROM exams ORDER BY year, semester, name;", null);
    	return c;
    	
    }
    
    public Cursor getExamsDetails( String str ){
    	
    	Cursor c;
		String[] selectionArgs = { str };
    	c=mDb.rawQuery("SELECT exams.name, credits, semester, year, note, data, vote, docenti.name, lode, type FROM exams, docenti WHERE exams.name = ? AND id_prof=docenti.id ;", selectionArgs);
    	return c;
    	
    }
   
  //AGGIUNGO DUE MIEI METODI PER RICERCARE TUTTI I PROF  [MASSI]
    public Cursor getDocenti(){
    	
    	Cursor c;
    	c=mDb.rawQuery("SELECT name FROM docenti ORDER BY name;", null); 
    	return c;
    	
    }
    public Cursor getDettagli_Docenti (String nome)
    {
    	Cursor c;
    	c=mDb.rawQuery("SELECT name , mail, meeting, phone FROM docenti WHERE name='"+nome+"';", null);
    	return c;
    }
   
    public Cursor getId_da_Docente (String nome)
    {
    	Cursor c;
    	
    	c=mDb.rawQuery("SELECT id FROM docenti WHERE name= '"+nome+"' ;", null);
    	
    	return c;
    }
    public Cursor getEsami_da_Docente (int id)
    {
    	Cursor c;
    	
    	c=mDb.rawQuery("SELECT name FROM exams WHERE id_prof= "+id+" ;", null);
    	
    	return c;
    }
    //----------
    public String getNote(String esame){
    	String s;
    	Cursor c;
    	
    	c=mDb.rawQuery("Select note FROM exams WHERE name = '"+esame+"';", null);
    	c.moveToNext();
    	s = c.getString(0);
    	
    	return s;
    }
    
    public void deleteAllExams(){
        mDb.delete("exams", null, null);
    }
    
    public void deleteAllProf(){
        mDb.delete("docenti", null, null);
    }
    
    public long insertProf(String name, String mail, String ricevimento, String phone){
		ContentValues cv = new ContentValues();
		cv.put("name", name);
		cv.put("mail", mail);
		cv.put("meeting", ricevimento);
		cv.put("phone", phone); 
		return mDb.insert("docenti", null, cv);
	}
    
    public boolean isProf(String name){
    	Cursor c;
    	
    	c = mDb.rawQuery("SELECT * FROM docenti WHERE name='"+name+"';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	return true;
    		}while(c.moveToNext());
    	return false;
    }
    
    public int isIdoneita(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams WHERE name='"+name+"';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("type");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int numberOkExams(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT COUNT(name) AS esami FROM exams WHERE vote >= 18 AND type=0;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("esami");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int numberCredits(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT SUM(credits) AS crediti FROM exams WHERE vote >= 18 AND type=0;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("crediti");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int numberAllExamsOk(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT COUNT(name) AS esami FROM exams WHERE vote >= 18;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("esami");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int numberAllCreditsOk(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT SUM(credits) AS crediti FROM exams WHERE vote >= 18;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("crediti");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    public int numberLode(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT COUNT(DISTINCT name) AS lodi FROM exams WHERE lode > 0;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("lodi");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public void insertVoto(int id, int voto){
		mDb.execSQL("UPDATE exams SET vote = "+ voto +" WHERE id = "+ id +";");
	}
    
    public void insertData(int id, String data){
		mDb.execSQL("UPDATE exams SET data = "+"'" +data +"'" +" WHERE id = "+ id +";");
	}
    
    public int sumVote(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT SUM(vote) AS voti FROM exams WHERE vote >= 18 AND type=0;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("voti");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int sumVotePonderate(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT SUM(vote*credits) AS voti FROM exams WHERE vote >= 18 AND type=0;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("voti");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int numTotalExams(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT COUNT(DISTINCT name) AS esami FROM exams;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("esami");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public int numTotalCredits(){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT SUM(credits) AS crediti FROM exams;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("crediti");
    	    	return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public Cursor examToModify(String str){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams WHERE name='" + str + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	return c;
    		}while(c.moveToNext());
    	return null;
    }
    
    public Cursor profToModify(String str){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM docenti WHERE name='" + str + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	return c;
    		}while(c.moveToNext());
    	return null;
    }
    
    public void loadNameProf(ArrayList<String> l){
    	Cursor c;
    	int nameCol;
    	
    	l.removeAll(l);
    	
    	c = mDb.rawQuery("SELECT * FROM docenti ORDER BY name;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("name");
    			l.add(c.getString(nameCol));
    		}while(c.moveToNext());
    }
    
    public int findPosProf(int id){
    	Cursor c;
    	int nameCol;
    	int counter = 0;
    	
    	c = mDb.rawQuery("SELECT * FROM docenti ORDER BY name;", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			if(c.getInt(nameCol) == id)
    			   break;
    			counter++;
    		}while(c.moveToNext());
    	
    	return counter;
    }
    
    public int getIdProf(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM docenti where name = '"+ name + "';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("id");
    			return c.getInt(nameCol);
    		}while(c.moveToNext());
    	return 0;
    }
    
    public boolean haveProfExams(int id){
    	Cursor c;
    	
    	c = mDb.rawQuery("SELECT * FROM exams where id_prof = "+ id + ";", null);
    	
    	if(c.moveToFirst())
    		do{
    			return true;
    		}while(c.moveToNext());
    	return false;
    }
    
    public void modifyExam(String old_name, String name, String note, int semestre, int anno, int id_docente, int voto, int crediti, String data, int lode, int tipo){
    	mDb.execSQL("UPDATE exams SET name ='"+name+"', note = '"+note+"', semester = "+semestre+", vote = "+voto+", id_prof = "+id_docente+", credits = "+crediti+", " +
    			     "year = "+anno+", data = '"+data+"', lode = " + lode + ", type = " + tipo + " WHERE name = '"+old_name+"';");
    }
    
    public void modifyProf(String old_name, String name, String mail, String phone, String ricevimento){
    	mDb.execSQL("UPDATE docenti SET name ='"+name+"', mail = '"+mail+"', phone = '"+phone+"', meeting = '"+ricevimento+"' WHERE name = '"+old_name+"';");
    }
    
    public void deleteSingleExam(int id){
    	mDb.execSQL("DELETE FROM exams WHERE id = " + id + ";");
    }
    
    public void deleteSingleProf(String nome){
    	mDb.execSQL("DELETE FROM docenti WHERE name = '" + nome + "';");
    }
    
    public void deleteExamByName(String name){
    	mDb.execSQL("DELETE FROM exams WHERE name = '" + name + "';");
    }
    
    public Cursor getExamsOrder(String order){
    	
    	Cursor c;
    	c = mDb.rawQuery("SELECT * FROM exams ORDER BY " + order + ";", null);
    	return c;
    	
    }
    
    public Cursor getProfOrder(String order){
    	
    	Cursor c;
    	c = mDb.rawQuery("SELECT * FROM docenti ORDER BY " + order + ";", null);
    	return c;
    	
    }
    
    public void setLode(String name, int lode){
    	mDb.execSQL("UPDATE exams SET lode = " + lode + " WHERE name = '"+ name +"' ;");
    }
    
    public void setType(String name, int tipo){
    	mDb.execSQL("UPDATE exams SET type = " + tipo + " WHERE name = '"+ name +"' ;");
    }
    
    public void setAlias(String name, String alias){
    	mDb.execSQL("UPDATE exams SET acronym = '" + alias + "' WHERE name = '"+ name +"' ;");
    }
    
    public Cursor personalquery(String query){
    	return mDb.rawQuery(query, null);
    }
   
    public boolean isYear(int year){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams WHERE year = "+year+";", null);
    	
    	if(c.moveToFirst())
    		do{
    			return true;	
    		}while(c.moveToNext());
    	return false;
    }
    
    public void deleteExamsForYear(int anno){
    	mDb.execSQL("DELETE FROM exams WHERE year = " + anno + ";");
    }
    
    public void deleteEmptyProf(){
    	mDb.execSQL("DELETE FROM docenti WHERE id NOT IN (SELECT id_prof FROM exams)");
    }
    
    public boolean isExam(String name){
    	Cursor c;
    	int nameCol;
    	
    	c = mDb.rawQuery("SELECT * FROM exams WHERE name='"+name+"';", null);
    	
    	if(c.moveToFirst())
    		do{
    	    	nameCol = c.getColumnIndex("name");
    	    	return true;
    		}while(c.moveToNext());
    	return false;
    }
}